package com.hrm.salarys.dao;

import com.hrm.domain.salarys.UserSalary;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.Map;

public interface UserSalaryDao extends PagingAndSortingRepository<UserSalary,String>, CrudRepository<UserSalary,String>, JpaRepository<UserSalary,String>, JpaSpecificationExecutor<UserSalary> {

    //nativeQuery = true使用本地sql，否则是jpql
    //value：查询的sql语句（bs_user和user_salary左外联查询）
    //countQuery：查询总记录的sql语句
    //Pageable：配置分页的基本数据（页码、每页条数）pageRequest是实现类
    @Query(nativeQuery = true,
            value="select bu.id,bu.username,bu.mobile,bu.work_number workNumber," +
                    "bu.in_service_status inServiceStatus,bu.department_name departmentName,bu.department_id departmentId,bu.time_of_entry timeOfEntry ," +
                    "bu.form_of_employment formOfEmployment ,sauss.current_basic_salary currentBasicSalary,sauss.current_post_wage currentPostWage from bs_user bu LEFT JOIN sa_user_salary sauss ON bu.id=sauss.user_id WHERE bu.company_id = ?1",
            countQuery = "select count(*) from bs_user bu LEFT JOIN sa_user_salary sauss ON bu.id=sauss.user_id WHERE bu.company_id = ?1"
    )
    Page<Map> findPage(String companyId, Pageable pageable);
}
